package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.UUID;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Repository;
import com.bringway.framework.dao.BaseJdbcDao;
import com.bringway.global.domain.SysUserDetails;
import com.bringway.global.domain.User;
import com.bringway.p2p.dao.P2PUserDao;
import com.bringway.p2p.util.MD5Util;
import net.sf.json.JSONObject;

/*
 * 
 * @author fan
 * @className MyUserDaoImpl
 * @description 用户操作持久层dao实现类
 * @date 2015年12月17日下午4:10:32
 */
@Repository
public class P2PUserDaoImpl extends BaseJdbcDao implements P2PUserDao {

	Logger logger = Logger.getLogger(P2PUserDaoImpl.class);

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.MyUserDao#getAllusers()
	 */
	@Override
	public List getAllusers(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		String gyh = parms.optString("GYH");
		Map<String, String> parmsMap = new HashMap<String, String>();
		sql.append(" SELECT ID, GYH, XM, ROLE_ID, GWMC FROM t_fm_sys_user ");
		sql.append(" where 1=1 ");
		if (StringUtils.isNotEmpty(gyh)) {
			sql.append(" and GYH = :GYH ");
			parmsMap.put("GYH", gyh);
		}
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.bringway.p2p.dao.P2PUserDao#getAllusersLimit(net.sf.json.JSONObject)
	 */
	@Override
	public List getAllusersLimit(JSONObject parms) {
		String XM = parms.optString("XM");
		String GWMC = parms.optString("GWMC");
		String GYH = parms.optString("GYH");
		String ROLE_ID = parms.optString("ROLE_ID");
		int start = parms.optInt("start");
		int limit = parms.optInt("limit");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(
				" SELECT t.ID, t.GYH,t.XM,t.GWMC,t1.ROLE_ID from t_fm_sys_user t left join  t_fm_sys_user_role t1 on t.ID = t1.USER_ID  where 1=1 ");
		if (StringUtils.isNotEmpty(XM)) {
			sql.append(" and t.XM like :XM ");
			parmsMap.put("XM", "%" + XM + "%");
		}
		if (StringUtils.isNotEmpty(GWMC)) {
			sql.append(" and t.GWMC like :GWMC ");
			parmsMap.put("GWMC", "%" + GWMC + "%");
		}
		if (StringUtils.isNotEmpty(GYH)) {
			sql.append(" and t.GYH like :GYH ");
			parmsMap.put("GYH", "%" + GYH + "%");
		}
		if (StringUtils.isNotEmpty(ROLE_ID)) {
			sql.append(" and t1.ROLE_ID like :ROLE_ID ");
			parmsMap.put("ROLE_ID", ROLE_ID);
		}
		sql.append(" LIMIT :start,:limit ");
		parmsMap.put("start", start);
		parmsMap.put("limit", limit);
		logger.info("getAllusersLimit sql is : " + sql.toString());
		logger.info("getAllusersLimit parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.P2PUserDao#addUser(net.sf.json.JSONObject)
	 */
	@Override
	public int addUser(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(
		" INSERT INTO d_ccb_fm.t_fm_sys_user (ID, GYH, USER_PASSWORD, XM, ROLE_ID,GWMC, OPER_USERID,SJHM) VALUES (:ID,:GYH,MD5(:USER_PASSWORD),:XM,:ROLEID,:GWMC, :OPER_USERID,:SJHM) ");
		parmsMap.put("ID", parms.optString("ID"));
		parmsMap.put("GYH", parms.optString("GYH"));
		parmsMap.put("SJHM", parms.optString("SJHM"));
		parmsMap.put("USER_PASSWORD", parms.optString("USER_PASSWORD"));
		parmsMap.put("XM", parms.optString("XM"));
		parmsMap.put("GWMC", parms.optString("GWMC"));
		parmsMap.put("ROLEID", parms.optString("ROLEID"));
		try {
			SysUserDetails sysUserDetails = (SysUserDetails) SecurityContextHolder.getContext().getAuthentication()
					.getPrincipal();
			parmsMap.put("OPER_USERID", sysUserDetails.getGyh());
		} catch (Exception e) {
			e.printStackTrace();
			parmsMap.put("OPER_USERID", "");
		}
		logger.info("addUser sql is : " + sql.toString());
		logger.info("addUser parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.P2PUserDao#deleteUser(net.sf.json.JSONObject)
	 */
	@Override
	public int deleteUser(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("id", parms.opt("id"));
		sql.append(" delete from d_ccb_fm.t_fm_sys_user where id = :id ");
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.P2PUserDao#updateUser(net.sf.json.JSONObject)
	 */
	@Override
	public int updateUser(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		String id = parms.optString("id");
		String xm = parms.optString("xm");
		String gwmc = parms.optString("gwmc");
		String role = parms.optString("role");
		sql.append(" UPDATE d_ccb_fm.t_fm_sys_user SET XM= :xm,GWMC= :gwmc ,ROLE_ID=:role WHERE ID= :id ");
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		paramsMap.put("id", id);
		paramsMap.put("xm", xm);
		paramsMap.put("gwmc", gwmc);
		paramsMap.put("role", role);
		logger.info("updateUser sql is " + sql.toString());
		logger.info("updateUser paramsMap is " + paramsMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.bringway.p2p.dao.P2PUserDao#resetPassword(net.sf.json.JSONObject)
	 */
	@Override
	public int resetPassword(JSONObject parms) {
		String id = parms.optString("id");
		String password = "111111";
		password = MD5Util.MD5Encode(password, "");
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE d_ccb_fm.t_fm_sys_user SET USER_PASSWORD = :password where ID=:id ");
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("password", password);
		parmsMap.put("id", id);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
	}

	@Override
	public User login(String gyh, String password) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT	t1.id,t1.gyh,t1.xm username,t1.USER_PASSWORD password,t2.ROLE_ID roleId,t3.name roleName,t1.SJHM sjhm");
		sql.append(" FROM t_fm_sys_user t1 ");
		sql.append(" LEFT JOIN t_fm_sys_user_role t2 ON t1.ID=t2.USER_ID ");
		sql.append(" LEFT JOIN t_fm_sys_role t3 on t2.ROLE_ID=t3.id ");
		sql.append(" where t1.gyh = :gyh and t1.USER_PASSWORD = :password ");
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("gyh", gyh);
		parmsMap.put("password", MD5Util.MD5Encode(password, ""));
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap, User.class);
	}

	@Override
	public SysUserDetails login(String gyh) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				" SELECT	t1.id ID,t1.gyh GYH,t1.xm USERNAME,t1.USER_PASSWORD USER_PASSWORD,t2.ROLE_ID ROLE_ID,t3.name ROLENAME,t1.SJHM SJHM ");
		sql.append(" FROM t_fm_sys_user t1 ");
		sql.append(" LEFT JOIN t_fm_sys_user_role t2 ON t1.ID=t2.USER_ID ");
		sql.append(" LEFT JOIN t_fm_sys_role t3 on t2.ROLE_ID=t3.id ");
		sql.append(" where t1.gyh = :gyh ");
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("gyh", gyh);
		logger.info("login sql is : " + sql.toString());
		logger.info("login parmsMap is : " + parmsMap);
		Map<String, Object> userMap = this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), parmsMap);
		SysUserDetails sysUserDetails = new SysUserDetails();
		if (null == userMap) {
			return null;
		} else {
			if (userMap.containsKey("ID")) {
				sysUserDetails.setId(userMap.get("ID").toString());
			}
			if (userMap.containsKey("GYH")) {
				sysUserDetails.setGyh(userMap.get("GYH").toString());
			}
			if (userMap.containsKey("SJHM")) {
				sysUserDetails.setSjhm(MapUtils.getString(userMap, "SJHM", ""));
			}
			if (userMap.containsKey("USERNAME")) {
				sysUserDetails.setUsername(userMap.get("USERNAME").toString());
			}
			if (userMap.containsKey("USER_PASSWORD")) {
				sysUserDetails.setPassword(userMap.get("USER_PASSWORD").toString());
			}
			if (userMap.containsKey("ROLE_ID")) {
				Object roleIdObj = userMap.get("ROLE_ID");
				if (null == roleIdObj) {
					sysUserDetails.setRoleId("");
				} else {
					sysUserDetails.setRoleId(roleIdObj.toString());
				}
			}
			return sysUserDetails;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.P2PUserDao#addUserRole(java.lang.String,
	 * java.lang.String)
	 */
	@Override
	public void addUserRole(String userId, String roleId, String XM) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				" INSERT INTO d_ccb_fm.t_fm_sys_user_role (ID, USER_ID, ROLE_ID, USER_NAME) VALUES (:id,:userId,:roleId,:XM) ");
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		parmsMap.put("id", UUID.randomUUID().toString());
		parmsMap.put("userId", userId);
		parmsMap.put("roleId", roleId);
		parmsMap.put("XM", XM);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
	}

	@Override
	public int getPageCount(String tableName) {
		return 0;
	}

	@Override
	public int comparePassword(JSONObject parms) {
		String GYH = parms.optString("gyh");
		String USER_PASSWORD = parms.optString("oldPassword");
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(*) from t_fm_sys_user where GYH = :GYH and USER_PASSWORD = md5('" + USER_PASSWORD
				+ "') ");
		Map<String, String> parmsMap = new HashMap<String, String>();
		parmsMap.put("GYH", GYH);
		// parmsMap.put("USER_PASSWORD", USER_PASSWORD);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap, Integer.class);
	}

	@Override
	public int changePassword(JSONObject parms) {
		String GYH = parms.optString("GYH");
		String newPassword = parms.optString("newPassword");
		StringBuffer sql = new StringBuffer();
		sql.append(" update t_fm_sys_user set USER_PASSWORD = md5('" + newPassword + "') where GYH = :GYH ");
		Map<String, String> parmsMap = new HashMap<String, String>();
		// parmsMap.put("newPassword", newPassword);
		parmsMap.put("GYH", GYH);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), parmsMap);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.P2PUserDao#getUserByGyh(java.lang.String)
	 */
	@Override
	public int getUserByGyh(String gyh) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(*) FROM d_ccb_fm.t_fm_sys_user t where t.GYH = :GYH ");
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		paramsMap.put("GYH", gyh);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramsMap, Integer.class);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.bringway.p2p.dao.P2PUserDao#getUserByGyhAndPassword(net.sf.json.
	 * JSONObject)
	 */
	@Override
	public int getUserByGyhAndPassword(JSONObject params) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		sql.append(" select count(*) from d_ccb_fm.t_fm_sys_user t where t.GYH = :GYH and t.USER_PASSWORD = md5('"
				+ params.optString("password") + "') ");
		// paramsMap.put("USER_PASSWORD", params.optString("password"));
		paramsMap.put("GYH", params.optString("username"));
		logger.info("getUserByGyhAndPassword sql is : " + sql.toString());
		logger.info("getUserByGyhAndPassword paramsMap is : " + paramsMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramsMap, Integer.class);
	}

	@Override
	public int getUsersItems(JSONObject parms) {
		String XM = parms.optString("XM");
		String GWMC = parms.optString("GWMC");
		String GYH = parms.optString("GYH");
		String ROLE_ID = parms.optString("ROLE_ID");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parmsMap = new HashMap<String, Object>();
		sql.append(
				" SELECT COUNT(*) CT from t_fm_sys_user t left join  t_fm_sys_user_role t1 on t.ID = t1.USER_ID  where 1=1 ");
		if (StringUtils.isNotEmpty(XM)) {
			sql.append(" and t.XM like :XM ");
			parmsMap.put("XM", "%" + XM + "%");
		}
		if (StringUtils.isNotEmpty(GWMC)) {
			sql.append(" and t.GWMC like :GWMC ");
			parmsMap.put("GWMC", "%" + GWMC + "%");
		}
		if (StringUtils.isNotEmpty(GYH)) {
			sql.append(" and t.GYH like :GYH ");
			parmsMap.put("GYH", "%" + GYH + "%");
		}
		if (StringUtils.isNotEmpty(ROLE_ID)) {
			sql.append(" and t1.ROLE_ID like :ROLE_ID ");
			parmsMap.put("ROLE_ID", ROLE_ID);
		}
		logger.info("getUsersItems sql is : " + sql.toString());
		logger.info("getUsersItems parmsMap is : " + parmsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), parmsMap, Integer.class);
	}

	@Override
	public int deleteUser_Menu(JSONObject parms) {
		String user_id = parms.optString("id");
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from t_fm_sys_user_menu where USER_ID = :id ");
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		paramsMap.put("id", user_id);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramsMap);
	}

	@Override
	public int deleteUser_Role(JSONObject parms) {
		String userid = parms.optString("id");
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from d_ccb_fm.t_fm_sys_user_role where USER_ID = :id ");
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		paramsMap.put("id", userid);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramsMap);
	}

	@Override
	public int updateAppUser(JSONObject parms) {
		StringBuffer sql = new StringBuffer();
		String yhm = parms.optString("yhm");
		String xm = parms.optString("xm");
		String gwmc = parms.optString("gwmc");
		String role = parms.optString("role");
		sql.append(
				" INSERT INTO d_ccb_fm.t_fm_app_root_user (ID, GYH, XM, GWMC,NAME) VALUES (uuid(),:yhm,:xm,:gwmc,:role) ");
		Map<String, String> paramsMap = new HashMap<String, String>();
		paramsMap.put("yhm", yhm);
		paramsMap.put("xm", xm);
		paramsMap.put("gwmc", gwmc);
		paramsMap.put("role", role);
		logger.info("updateAppUser sql is " + sql.toString());
		logger.info("updateAppUser paramsMap is " + paramsMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramsMap);

	}

	@Override
	public List<Map<String, Object>> listJkrInfo(JSONObject params) {
		Integer start = params.optInt("start");
		Integer limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String yhm = params.optString("yhm");
		String sjhm = params.optString("sjhm");
		String sqr = params.optString("sqr");
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select t2.ID,t2.USER_NAME,t2.SJHM,date_format(t2.CJRQ,'%Y-%m-%d') CJRQ,t1.GWLX,t3.SQR_SJHM,t3.OPEN_GYH,date_format(t3.OPEN_DATE,'%Y-%m-%d') OPEN_DATE ");
		sql.append(
				"from (select GWLX,GYH from d_hd_p2p.t_zhfw_jbxx where GWLX='借款人') t1 left join d_hd_p2p.t_zhfw_user t2");
		sql.append(" on t2.USER_NAME=t1.GYH left join d_ccb_fm.t_fm_sys_log_zcjkr t3 on t2.USER_NAME=t3.USER_NAME");
		sql.append(" where 1=1 ");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t2.CJRQ,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t2.CJRQ,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and t2.USER_NAME=:yhm");
			paramMap.put("yhm", yhm);
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and t2.SJHM=:sjhm");
			paramMap.put("sjhm", sjhm);
		}
		if (StringUtils.isNotEmpty(sqr)) {
			sql.append(" and t3.SQR_SJHM=:sqr");
			paramMap.put("sqr", sqr);
		}
		sql.append(" order by date_format(t2.CJRQ,'%Y-%m-%d %H:%i:%s') desc");
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listJkrInfo sql is " + sql.toString());
		logger.info("listJkrInfo paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getJkrInfoCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String yhm = params.optString("yhm");
		String sjhm = params.optString("sjhm");
		String sqr = params.optString("sqr");
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select count(*) from (select GWLX,GYH from d_hd_p2p.t_zhfw_jbxx where GWLX='借款人') t1 left join d_hd_p2p.t_zhfw_user t2");
		sql.append(" on t2.USER_NAME=t1.GYH left join d_ccb_fm.t_fm_sys_log_zcjkr t3 on t2.USER_NAME=t3.USER_NAME");
		sql.append(" where 1=1 ");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t2.CJRQ,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t2.CJRQ,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and t2.USER_NAME=:yhm");
			paramMap.put("yhm", yhm);
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and t2.SJHM=:sjhm");
			paramMap.put("sjhm", sjhm);
		}
		if (StringUtils.isNotEmpty(sqr)) {
			sql.append(" and t3.SQR_SJHM=:sqr");
			paramMap.put("sqr", sqr);
		}
		logger.info("getJkrInfoCount sql is " + sql.toString());
		logger.info("getJkrInfoCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> listSqrSjhm() {
		String sql = "select CONFIG_VALUE VALUE,CONFIG_VALUE TEXT from d_ccb_fm.t_fm_sys_config where CONFIG_KEY like 'JKR_ZCSQ_SJHM%' group by CONFIG_VALUE order by CONFIG_KEY";
		Map<String, Object> paramMap = new HashMap<>();
		logger.info("listSqrSjhm sql is " + sql);
		return this.getNamedParameterJdbcTemplate().queryForList(sql, paramMap);
	}

	@Override
	public boolean isBeUsername(String user_name) {
		String sql = "select count(*) from d_hd_p2p.t_zhfw_user where DISABLED='1' and USER_NAME=:user_name";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("user_name", user_name);
		logger.info("IsBeUsername sql is " + sql);
		logger.info("IsBeUsername paramMap is " + paramMap);
		int count = this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
		if (count > 0) {
			return false;
		} else {
			return true;
		}
	}

	@Override
	public boolean isBeSjhm(String sjhm) {
		String sql = "select count(*) from d_hd_p2p.t_zhfw_user where DISABLED='1' and SJHM=:sjhm";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("sjhm", sjhm);
		logger.info("isBeSjhm sql is " + sql);
		logger.info("isBeSjhm paramMap is " + paramMap);
		int count = this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
		if (count > 0) {
			return false;
		} else {
			return true;
		}
	}

	@Override
	public void deleteDisUser(String username, String sjhm) {
		if (isBeUsername(username)&&isBeSjhm(sjhm)) {
			//user
			String sql = "delete from d_hd_p2p.t_zhfw_user where DISABLED='0' and (USER_NAME=:username or SJHM=:sjhm)";
			Map<String, Object> paramMap = new HashMap<>();
			paramMap.put("username", username);
			paramMap.put("sjhm", sjhm);
			logger.info("deleteDisUser sql is " + sql);
			logger.info("deleteDisUser paramMap is " + paramMap);
			this.getNamedParameterJdbcTemplate().update(sql, paramMap);
			//jbxx
			String sql_jbxx = "delete from d_hd_p2p.t_zhfw_jbxx where GYH=:username or SJHM=:sjhm";
			logger.info("deleteDisUser sql_jbxx is " + sql_jbxx);
			this.getNamedParameterJdbcTemplate().update(sql_jbxx, paramMap);
			//user_role
			String sql_role = "delete from d_hd_p2p.t_zhfw_user_role where USERNAME=:username or SJHM=:sjhm";
			logger.info("deleteDisUser sql_role is " + sql_role);
			this.getNamedParameterJdbcTemplate().update(sql_role, paramMap);
		}
	}

	@Override
	public int insertUser(String username, String sjhm, String pwdDlmm, String sqm) {
		String sql = "insert into d_hd_p2p.t_zhfw_user(ID, USER_NAME, USER_PASSWORD, DISABLED, SJHM, YZM, XYJB, CJ_TYPE)"
				+ " values(uuid(),:username,md5(:pwdDlmm),'1',:sjhm,:sqm,'A','fm')";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("username", username);
		paramMap.put("sjhm", sjhm);
		paramMap.put("pwdDlmm", pwdDlmm);
		paramMap.put("sqm", sqm);
		logger.info("insertUser sql is " + sql);
		logger.info("insertUser paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int insertJbxx(String username, String sjhm, String pwdJymm) {
		String sql = "insert into d_hd_p2p.t_zhfw_jbxx(ID, GYH,SSDWM,GWLX,SJHM) "
				+ "values(uuid(),:username,'888888','借款人',:sjhm)";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("username", username);
		paramMap.put("sjhm", sjhm);
		paramMap.put("pwdJymm", pwdJymm);
		logger.info("insertJbxx sql is " + sql);
		logger.info("insertJbxx paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int insertRole(String username, String sjhm) {
		String sql = "insert into d_hd_p2p.t_zhfw_user_role(ID, USERNAME, ROLE_ID, SJHM) values(uuid(),:username,'3',:sjhm)";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("username", username);
		paramMap.put("sjhm", sjhm);
		logger.info("insertRole sql is " + sql);
		logger.info("insertRole paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int insertTgcode(String username, String sjhm) {
		Map<String, Object> paramMap = new HashMap<>();
		// 获取未使用的推广码
		String isHasHisTgcode_sql = "select count(*) from d_hd_p2p.t_zhfw_user_tgcode where user_name =:username";
		paramMap.put("username", username);
		String tgCode = null;
		if (this.getNamedParameterJdbcTemplate().queryForInt(isHasHisTgcode_sql, paramMap) > 0) {
			logger.info("新用户注册，已存在历史推广码，无法生成新的推广码");
		} else {
			// 获取未使用的推广码
			do {
				tgCode = "";
				Random random = new Random();
				// 参数length，表示生成几位随机数
				for (int i = 0; i < 8; i++) {
					String charOrNum = random.nextInt(2) % 2 == 0 ? "char" : "num";
					// 输出字母还是数字
					if ("char".equalsIgnoreCase(charOrNum)) {
						// 输出是大写字母还是小写字母
						int temp = random.nextInt(2) % 2 == 0 ? 65 : 97;
						tgCode += (char) (random.nextInt(26) + temp);
					} else if ("num".equalsIgnoreCase(charOrNum)) {
						tgCode += String.valueOf(random.nextInt(10));
					}
				}
			} while (this.isBeTgcode(tgCode));
		}
		String sql = "insert into d_hd_p2p.t_zhfw_user_tgcode(ID,USER_NAME,SJHM,TGCODE,IS_JT_YG) values(uuid(),:username,:sjhm,:tgCode,'0')";
		paramMap.put("username", username);
		paramMap.put("sjhm", sjhm);
		paramMap.put("tgCode", tgCode);
		logger.info("insertTgcode sql is " + sql);
		logger.info("insertTgcode paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int insertJkrLog(String username, String sqr, String openGyh) {
		String sql = "insert into d_ccb_fm.t_fm_sys_log_zcjkr(USER_NAME, SQR_SJHM, OPEN_GYH, OPEN_DATE) values(:username,:sqr,:openGyh,now())";
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("username", username);
		paramMap.put("sqr", sqr);
		paramMap.put("openGyh", openGyh);
		logger.info("insertJkrLog sql is " + sql);
		logger.info("insertJkrLog paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	public boolean isBeTgcode(String tgCode) {
		String sql = "select count(*) from d_hd_p2p.t_zhfw_user_tgcode where tgcode=:tgCode";
		logger.info("isBeTgcode sql is : " + sql);
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("tgCode", tgCode);
		logger.info("isBeTgcode paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap) > 0;
	}
}
